package aaa.database;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;

import aaa.exception.AAAException;
import aaa.exception.AAAInternalException;
import aaa.exception.UnknownScheduleException;
import aaa.schedule.Lesson;
import aaa.schedule.Schedule;
import aaa.schedule.ScheduleConstants;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.DatabaseUtils.InsertHelper;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class Database implements DatabaseConstants, ScheduleConstants {

	private SQLiteDatabase bdd ;

	private CreateDatabases cd ;
	private InsertHelper lessonInsert ;
	
	protected static Hashtable<Context, CreateDatabases> databases = new Hashtable<Context, CreateDatabases>() ;

	public Database (Context context) {
		if (databases.containsKey(context)) {
			this.cd = databases.get(context) ;
		} else {
			this.cd = new CreateDatabases(context, NOM_BDD, null, VERSION_BDD);
			databases.put(context, this.cd) ;
		}// if
	}// Database(Context)

	public void open() {
		if (this.bdd != null && this.bdd.isOpen()) {
			Log.e("aaa", "Database already opened !") ;
			return ;
		}// if
		//on ouvre la BDD en écriture
		this.bdd = this.cd.getWritableDatabase();
		this.lessonInsert = new InsertHelper(this.bdd, TABLE_LESSON) ;
	}

	public void close() {
		//on ferme l'accès à la BDD
		this.lessonInsert.close() ;
		this.bdd.close();
	}

	//----------------------------------------EDT ---------------------------------------------

	public long insertSchedule(Schedule s){
		this.open() ;
		//Création d'un ContentValues (fonctionne comme une HashMap)
		ContentValues values = new ContentValues();
		//on lui ajoute une valeur associée à une clé (qui est le nom de la colonne dans laquelle on veut mettre la valeur)
		values.put(SCH_COL_PROJECT_ID, s.getProjectId());
		values.put(SCH_COL_NAME, s.getName());
		values.put(SCH_COL_RESSOURCE_ID, s.getRessources());
		values.put(SCH_COL_PERIOD, s.getPeriod());
		values.put(SCH_COL_LAST_MODIF, s.getLastModif());
		values.put(SCH_COL_NOTIFY, s.getNotify());
		values.put(SCH_COL_CREATED, s.getCreated());
		values.put(SCH_COL_STATUS, ScheduleConstants.STATUS_OTHER) ;
		long resInsert = this.bdd.insert(TABLE_SCHEDULE, null, values) ;
		for(Lesson l : s) {
			this.insertLesson(l) ;
		}// for
		this.close() ;
		return resInsert ;
	}

	public int updateSchedule(String name, Schedule s){
		this.open() ;
		//La mise à jour d'un EDT dans la BDD fonctionne plus ou moins comme une insertion
		//il faut simple préciser quelle EDT on doit mettre à jour grâce à son nom
		String newName = s.getName() ;
		ContentValues values = new ContentValues();
		values.put(SCH_COL_PROJECT_ID, s.getProjectId());
		values.put(SCH_COL_NAME, newName);
		values.put(SCH_COL_RESSOURCE_ID, s.getRessources());
		values.put(SCH_COL_PERIOD, s.getPeriod());
		values.put(SCH_COL_LAST_MODIF, s.getLastModif());
		values.put(SCH_COL_NOTIFY, s.getNotify());
		values.put(SCH_COL_CREATED, s.getCreated());
		values.put(SCH_COL_STATUS, ScheduleConstants.STATUS_OTHER) ;
		int resUpdate = this.bdd.update(TABLE_SCHEDULE, values, String.format("%s = \"%s\"", SCH_COL_NAME, name), null);
		// UPDATE LESSON SET SCHEDULE = s.getName() WHERE SCHEDULE = name only if name != s.getName() 
		if (!newName.equals(name)) {
			values = new ContentValues();
			values.put(LES_COL_SCHEDULE, s.getName());
			this.bdd.update(TABLE_LESSON, values, String.format("%s = \"%s\"", LES_COL_SCHEDULE, name), null);
		}// if
		this.close() ;
		return resUpdate ;
	}

	public int removeScheduleWithName(String name){
		this.open() ;
		//Suppression d'un EDT de la BDD
		this.removeScheduleLessonsWithName(name);
		int resDel = this.bdd.delete(TABLE_SCHEDULE, String.format("%s = \"%s\"", SCH_COL_NAME, name), null);
		this.close() ;
		return resDel ;
	}

	protected int removeScheduleLessonsWithName(String name){
		//Suppression d'un EDT de la BDD
		int resDel = this.bdd.delete(TABLE_LESSON, String.format("%s = \"%s\"", LES_COL_SCHEDULE, name), null) ;
		return resDel ;
	}

	public Schedule getScheduleWithName(String name) throws AAAException {
		this.open() ;
		//Récupère dans un Cursor les valeurs correspondantent à un edt contenu dans la BDD
		Cursor c = this.bdd.query(TABLE_SCHEDULE, new String[] {SCH_COL_NAME, SCH_COL_PROJECT_ID, SCH_COL_RESSOURCE_ID, SCH_COL_PERIOD, SCH_COL_LAST_MODIF, SCH_COL_NOTIFY, SCH_COL_CREATED}, SCH_COL_NAME + " LIKE \"" + name +"\"", null, null, null,null, null);
		Schedule s = Database.cursorToSchedule(c);
		c.close() ;
		this.close() ;
		if (s == null) {
			throw new UnknownScheduleException(name) ;
		}// if
		return s ;
	}

	//Cette méthode permet de convertir un cursor en un EDT
	public static Schedule cursorToSchedule(Cursor c){
		Schedule s = null ;
		if (c.moveToNext()) {
			//On créé un livre
			s = new Schedule();
			//on lui affecte toutes les infos grâce aux infos contenues dans le Cursor
			s.setName(c.getString(SCH_NUM_COL_NAME));               
			s.setProjectId(c.getInt(SCH_NUM_COL_PROJECT_ID));
			s.setRessources(c.getString(SCH_NUM_COL_RESSOURCE_ID));
			s.setPeriod(c.getInt(SCH_NUM_COL_PERIOD));
			s.setLastModif(c.getString(SCH_NUM_COL_LAST_MODIF));
			s.setNotify(c.getInt(SCH_NUM_COL_NOTIFY));
			s.setCreated(c.getString(SCH_NUM_COL_CREATED));
		}

		//On retourne l'EDT
		return s;
	}

	/**
	 * Returns an array filled with the names of the schedules found
	 * in the database.
	 * 
	 * @return	array containing the names of the schedules
	 */
	public String[] getSchedulesName() {
		this.open() ;
		Cursor c = this.bdd.query(TABLE_SCHEDULE, new String[]{SCH_COL_NAME}, 
				null, null, null, null, "NAME ASC", null) ;
		ArrayList<String> liste = new ArrayList<String>() ;
		while (c.moveToNext()) {
			liste.add(c.getString(0)) ;
		}// while
		c.close();
		this.close() ;
		return liste.toArray(new String[liste.size()]);
	}

	//------------------------------------------------------------------------------------------------
	//----------------------------------------- Cours --------------------------------------------

	private long insertLesson(Lesson l) {
		// Get the InsertHelper ready to insert a single row
		this.lessonInsert.prepareForInsert();

		// Add the data for each column
		this.lessonInsert.bind(1+LES_NUM_COL_LESSON_ID, l.getLessonId()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_TITLE, l.getTitle()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_TIME, l.getTime()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_SCHEDULE, l.getSchedule()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_TYPE, l.getType()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_ROOM, l.getRoom()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_GROUP, l.getGroups()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_TEACHER, l.getTeacher()) ;
		this.lessonInsert.bind(1+LES_NUM_COL_LAST_MODIF, l.getCreated()) ;

		// Insert the row into the database.
		long resIns = this.lessonInsert.execute() ;
		return resIns ;
	}// insertLesson(Lesson)

	public int updateLesson(long oldId, String oldSchedule, Lesson newLesson){
		this.open() ;
		//La mise à jour d'un cours dans la BDD fonctionne plus ou moins comme une insertion
		//il faut simple préciser quel cours on doit mettre à jour grâce à son ID
		ContentValues values = new ContentValues();
		values.put(LES_COL_LESSON_ID, newLesson.getLessonId());
		values.put(LES_COL_TITLE, newLesson.getTitle());
		values.put(LES_COL_TIME, newLesson.getTime());
		values.put(LES_COL_SCHEDULE, newLesson.getSchedule());
		values.put(LES_COL_TYPE, newLesson.getType());
		values.put(LES_COL_ROOM, newLesson.getRoom());
		values.put(LES_COL_GROUP, newLesson.getRoom());
		values.put(LES_COL_TEACHER, newLesson.getTeacher());
		values.put(LES_COL_LAST_MODIF, newLesson.getCreated());
		int resUpt = this.bdd.update(TABLE_LESSON, values, 
				String.format("%s = '%s' AND %s = '%s'", LES_COL_LESSON_ID, oldId, LES_COL_SCHEDULE, oldSchedule), 
				null) ;
		this.close() ;
		return resUpt ;
	}


	public int removeLessonWithID(int id){
		this.open() ;
		//Suppression d'un cours de la BDD
		int resDel = this.bdd.delete(TABLE_LESSON, LES_COL_LESSON_ID +" = "+id, null);
		this.close() ;
		return resDel ;
	}

	public Lesson getLessonWithID(int id, String scheduleName){
		this.open() ;
		//Récupère dans un Cursor les valeurs correspondantent à un cours contenu dans la BDD
		Cursor c = this.bdd.query(TABLE_LESSON, 
				new String[] {LES_COL_LESSON_ID, LES_COL_TITLE, LES_COL_TIME, LES_COL_SCHEDULE, LES_COL_TYPE, 
				LES_COL_LAST_MODIF, LES_COL_ROOM, LES_COL_GROUP, LES_COL_TEACHER}, 
				String.format("%s = '%s' AND %s = '%s'", LES_COL_LESSON_ID, id, LES_COL_SCHEDULE, scheduleName), 
				null, null, null, null, null);
		Lesson lesson = Database.cursorToLesson(c) ;
		c.close() ;
		this.close() ;
		return lesson ;
	}

	//Cette méthode permet de convertir un cursor en un cours
	public static Lesson cursorToLesson(Cursor c){
		Lesson l = null ;
		if (c.moveToNext()) {
			l = new Lesson();
			//on lui affecte toutes les infos grâce aux infos contenues dans le Cursor             
			l.setLessonId(c.getInt(LES_NUM_COL_LESSON_ID));
			l.setTitle(c.getString(LES_NUM_COL_TITLE));
			l.setTime(c.getString(LES_NUM_COL_TIME));
			l.setSchedule(c.getString(LES_NUM_COL_SCHEDULE));
			l.setType(c.getInt(LES_NUM_COL_TYPE));
			l.setRoom(c.getString(LES_NUM_COL_ROOM));
			l.setGroups(c.getString(LES_NUM_COL_GROUP));
			l.setTeachers(c.getString(LES_NUM_COL_TEACHER));
			l.setCreated(c.getString(LES_NUM_COL_LAST_MODIF));
		}// if

		//On retourne le cours
		return l;
	}

	/**
	 * Returns an array filled with the lesson found
	 * in the database.
	 * 
	 * @return	array containing the names of the schedules
	 */
	public Lesson[] getLessonByScheduleName(String schedule) {
		this.open() ;
		Cursor c = this.bdd.query(TABLE_LESSON, 
				new String[] {LES_COL_LESSON_ID, LES_COL_TITLE, LES_COL_TIME, LES_COL_SCHEDULE, LES_COL_TYPE,
				LES_COL_ROOM, LES_COL_GROUP, LES_COL_TEACHER}, 
				String.format("%s = '%s'", LES_COL_SCHEDULE, schedule), 
				null, null, null, null, null) ;
		ArrayList<Lesson> liste = new ArrayList<Lesson>() ;
		Lesson l = Database.cursorToLesson(c) ;
		while (l != null) {
			liste.add(l) ;
			l = Database.cursorToLesson(c) ;
		}// while
		c.close() ;
		this.close() ;
		return liste.toArray(new Lesson[liste.size()]);
	}

	public Cursor getLessonsCursor(Schedule s){
		this.open() ;
		Cursor c = this.bdd.query(TABLE_LESSON, 
				new String[] {LES_COL_LESSON_ID, LES_COL_TITLE, LES_COL_TIME, LES_COL_SCHEDULE, LES_COL_TYPE, LES_COL_LAST_MODIF, LES_COL_ROOM, LES_COL_GROUP, LES_COL_TEACHER}, 
				String.format("%s = '%s' AND TIME >= strftime('%%Y/%%m/%%d %%H:%%M', 'now')", LES_COL_SCHEDULE, s.getName()), 
				null,  // SELECTION ARRAYS (binding ? to variables)
				null,  // GROUP BY
				null,  // HAVING
				"TIME ASC", // ORDER BY
				null) ;// LIMIT
		//this.close() ;
		return c ;
	}// getLessonsCursor(Schedule)

	public void insertLessons(ArrayList<Lesson> lessons) throws AAAException {		 
		try {
			this.open() ;
			this.bdd.beginTransaction() ;
			for(Lesson l : lessons) {
				this.insertLesson(l) ;
			}// for
			this.bdd.setTransactionSuccessful() ;
		} catch (IllegalStateException e) {
			throw new AAAInternalException("Erreur lors de l'insertion de cours.") ;
		} finally {
			this.bdd.endTransaction() ;
			this.close() ;
		}// try
	}// insertLessons(ArrayList<Lesson>)

	public void updateLessons(ArrayList<Lesson> lessons) throws AAAException {
		for(Lesson l : lessons) {
			this.updateLesson(l.getLessonId(), l.getSchedule(), l) ;
		}// for
	}// updateLessons(ArrayList<Lesson>)

	public int deleteLessons(ArrayList<Lesson> lessons, String scheduleName) {
		this.open() ;
		// Build the where condition
		StringBuilder ids = new StringBuilder() ;
		for(Lesson l : lessons) {
			ids.append(",") ;
			ids.append(l.getLessonId()) ;
		}// for
		String where = String.format("%s IN (%s) AND %s = '%s'", 
				LES_COL_LESSON_ID, ids.substring(1),
				LES_COL_SCHEDULE, scheduleName) ;
		//Suppression d'un cours de la BDD
		int resDel = this.bdd.delete(TABLE_LESSON, where, null);
		this.close() ;
		return resDel ;
	}// deleteLessons(ArrayList<Lesson>)

	public long setLastModifSchedule(String scheduleName) {
		this.open() ;
		ContentValues values = new ContentValues();
		values.put(SCH_COL_LAST_MODIF, new SimpleDateFormat("yyyy/MM/dd HH:mm").format(new Date()));
		values.put(SCH_COL_STATUS, ScheduleConstants.STATUS_UPDATED) ;
		long resUpt = this.bdd.update(TABLE_SCHEDULE, values, 
				String.format("%s = '%s'", SCH_COL_NAME, scheduleName), 
				null) ;
		this.close() ;
		return resUpt ;
	}

	public ArrayList<String> getRooms(ArrayList<String> potentialRooms) {
		this.open() ;
		StringBuffer list = new StringBuffer() ;
		for(String room : potentialRooms) {
			list.append(", ") ;
			list.append(DatabaseUtils.sqlEscapeString(room)) ;
		}// for

		String where = String.format("%s IN (%s)", ROOM_COL_NAME, list.substring(2)) ;
		Cursor roomCursor = this.bdd.query(TABLE_ROOM, new String[]{"DISTINCT " + ROOM_COL_NAME}, where, null, null, null, null) ;
		ArrayList<String> rooms = new ArrayList<String>() ;
		while (roomCursor.moveToNext()) {
			rooms.add(roomCursor.getString(0)) ;
		}// while
		roomCursor.close() ;
		this.close() ;

		return rooms ;
	}

	public int setScheduleStatus(String scheduleName, int status) {
		this.open() ;
		ContentValues values = new ContentValues();
		values.put(SCH_COL_STATUS, status) ;
		int resUpt = this.bdd.update(TABLE_SCHEDULE, values, 
				String.format("%s = '%s'", SCH_COL_NAME, scheduleName), 
				null) ;
		this.close() ;
		return resUpt ;
	}

	public int getScheduleStatus(String scheduleName) {
		this.open() ;
		
		String where = String.format("%s = %s", ROOM_COL_NAME, DatabaseUtils.sqlEscapeString(scheduleName)) ;
		Cursor scheduleCursor = this.bdd.query(TABLE_SCHEDULE, new String[]{SCH_COL_STATUS}, where, null, null, null, null) ;
		int status = STATUS_OTHER ;
		if (scheduleCursor.moveToNext()) {
			status = scheduleCursor.getInt(0) ;
		}// if
		scheduleCursor.close() ;
		this.close() ;
		return status ;
	}

	//------------------------------------------------------------------------------------------------
}
